package com.db.dao;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.db.curd.tool.FieldOp;


 
/**
 * 利用java的反射机制
 * 通用DAO，把大部分DAO都需要执行的数据库操作都定义好
 * http://www.oschina.net/code/snippet_214234_16170
 * 
 * 追加了单例模式 效率
 * 
 * @author Administrator
 * 
 */
public abstract class BaseDAO {
	


	
    private QueryRunner qr= null;
    private DataSource dataSource =null;
    private Connection connection = null;
    

 	
 	
    /**
     * qr dataSource connection 实例化
     * @throws SQLException 
     */
    abstract void init() throws SQLException;

  
    
	
	
	
	public BaseDAO() throws SQLException {
		this.init();
	}
	/**
     * exzample:<BR>select * from Student where age>21;
     * 
     * @param sql
     * @param clazz
     * @return
	 * @throws SQLException 
     */
    public List querySome(String sql, Class clazz) throws SQLException {
    	Connection conn = this.getConnection();
    	
    	
        List beans = null;
        try {
            //QueryRunner qRunner = new QueryRunner();
            beans = (List) qr.query(conn, sql, new BeanListHandler(clazz));
            return beans;
        } catch (SQLException e) {
            e.printStackTrace();
            
        }
        return beans;
    }
    
   
    public List querySome(String sql,Object[] conditions,Class clazz){
    	List beans = null;
    	Connection conn = this.getConnection();
    	 try {
             //QueryRunner qRunner = new QueryRunner();
             beans = (List) qr.query(conn, sql,conditions, new BeanListHandler(clazz));
             
         } catch (SQLException e) {
             e.printStackTrace();
         }
    	
    	return beans;
    }
    
 
    /**
     * 获得一行结果
     * 
     * @param sql
     * @param clazz
     * @return
     */
    //Statement
    
    public Object queryOne(String sql, Class clazz) {
    	Connection conn = this.getConnection();
        List beans = null;
        Object obj = null;
        try {
            beans = (List) qr.query(conn, sql, new BeanListHandler(clazz));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans != null && !beans.isEmpty()) { // 注意这里 
            obj=beans.get(0);
        }
        return obj;
    }
    
    public Object queryOne(String sql,Object[] conditions, Class clazz) {
    	Connection conn = this.getConnection();
        List beans = null;
        Object obj = null;
        try {
            beans = (List) qr.query(conn, sql,conditions, new BeanListHandler(clazz));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans != null && !beans.isEmpty()) { // 注意这里 
            obj=beans.get(0);
        }
        return obj;
    }
 
    /**查询所有<BR>
     * select * from tableName
     * 
     * @param clazz
     * @param tableName
     * @return
     */
    public List getAll(Class clazz, String tableName) {
    	Connection conn = this.getConnection();
        List beans = null;
        try {
            beans = (List) qr.query(conn, "select * from " + tableName,
                    new BeanListHandler(clazz));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return beans;
    }
    /**更新一条记录
     * <BR>删除和修改
     * example:<BR>
     * update auth_role set name='xxx', pwd='www',age=12 where id=2; 
     * @param clazz
     * @param tableName
     * @return
     */
    public int update(String sql){
    	Connection conn = this.getConnection();
        try {
            return qr.update(conn, sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }
    /**---id自增长的
     * insert into tableName values(default,javabean.getName(),javabean.getPwd(),javabean.getAge,java)
     * insert into tableName ()
     * @param object
     * @return
     */
    public int insertWithDefaulId(String tableName,Object bean){
    	Connection conn = this.getConnection();
        Class clazz=bean.getClass();
        String [] fields=FieldOp.getAttributeNames(clazz);
        String sql=FieldOp.getInsertSQLWithDefault(tableName, fields);
         
         
         
        return 0;
    }
    /** ---id非自增长的
     * insert into tableName values(javabean.getId(),javabean.getName(),javabean.getPwd(),javabean.getAge,java)
     * @param clazz
     * @param tableName
     * @param bean
     * @return
     */
    public int insertNoDefaultId(String tableName,Object bean){
    	Connection conn = this.getConnection();
        Class clazz=bean.getClass();
        String [] fields=FieldOp.getAttributeNames(clazz);
        Method[] getters=FieldOp.alignSetters(fields, clazz.getDeclaredMethods());
        String sql=FieldOp.getInsertSQLNoDefaultId(tableName, fields);
        Object[] params=FieldOp.invokeResut(fields, getters, bean); 
        try {
            return qr.update(conn,sql, params);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }






	public QueryRunner getQr() {
		return qr;
	}






	public void setQr(QueryRunner qr) {
		this.qr = qr;
	}






	public DataSource getDataSource() {
		return dataSource;
	}






	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}






	public Connection getConnection() {
		return connection;
	}






	public void setConnection(Connection connection) {
		this.connection = connection;
	}
    
    
    
}
